--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_gam_operamod;

CREATE TABLE se_gam_operamod AS (SELECT * FROM &erp.gam_operamod WHERE 1=2);


--SELECT * FROM UV_PENT_GAMMES_emploi e  /*gamme standard entete*/
--SELECT * FROM UV_PDET_GAMMES d  /*gamme standard ligne*/
--SELECT * FROM  UV_PPT_GAMMES  /*gamme entete article*/
--SELECT * FROM UV_PPT_GAMMES_DETGAM where id like '%P10832741094%' ORDER BY 1/*gamme ligne article */



 INSERT INTO se_gam_operamod

    (SELECT ope.id opera_cod,
            '+' modulation_val,
            0 enreg_id,
            'CH' prepa_untps_cod,
            tpreg prepamach_tps,
            tpusi/100 reamach_val,
            1 bons_nb
            /*,*/

        FROM uv_poperations ope
        WHERE EXISTS (SELECT 1 FROM UV_PENT_GAMMES ge,UV_PDET_GAMMES gl,UV_PDET_GAMMES std_lig, UV_PPT_GAMMES_DETGAM art_lig
                      WHERE std_lig.nogamme=ge.id AND art_lig.detgam=std_lig.id
                        AND ge.id=gl.nogamme AND ope.id=gl.operation)
        ) ;



 UPDATE se_gam_operamod tf SET enreg_id=(select enreg_id FROM &erp.gam_operamod f WHERE f.opera_cod=tf.opera_cod AND f.modulation_val=tf.modulation_val) ;
 UPDATE se_gam_operamod tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('opera_cod@modulation_val','GAM_OPERAMOD','O') ;

 commit;
 
 
 
select * from (
SELECT &outils.stragg(source) source, opera_cod, modulation_val, enreg_id, prepa_untps_cod, prepamach_tps, reamach_val, bons_nb
FROM (
  SELECT 'uv' source ,g.* FROM se_gam_operamod g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gam_operamod g

  )
GROUP BY opera_cod, modulation_val, enreg_id, prepa_untps_cod, prepamach_tps, reamach_val, bons_nb
HAVING Count(1)!=2) r
ORDER BY  opera_cod ;